import utils
import os
import isbnlib
import time

conn = utils.init_db('mysql', 'isbn_cover')
cur = conn.cursor()
sql = "insert ignore into cover_nopic(isbn13,isbn10) values('{}','{}')"


def coverlistinsert():
    file = open(r'E:\lqx\cover\智图isbn无图片_20190124.txt', 'r', encoding='utf-8')
    time_start = time.time()
    # file = open(r'E:\lqx\cover\test.txt', 'r')
    cnt = 0
    for line in file:
        line = line.strip()
        line = line.replace(';', ',')
        line = line.replace('/', ',')
        line = line.replace('(', ',')
        line = line.replace(' ', ',')
        line = line.replace('\\', ',')
        # print(line)
        isblist = line.split(',')
        for isb in isblist:
            isb = isb.strip()
            isb = isbnlib.clean(isb)
            isbn10 = ''
            isbn13 = ''
            if (len(isb) == 10) and isbnlib.is_isbn10(isb):
                isbn10 = isb
                isbn13 = isbnlib.to_isbn13(isbn10)
            elif (len(isb) == 13) and isbnlib.is_isbn13(isb):
                isbn13 = isb
                isbn10 = isbnlib.to_isbn10(isbn13)
            if not isbn10:
                isbn10 = ''
            if not isbn13:
                isbn13 = ''

            if (not isbn10) and (not isbn13):
                continue

            # print(isb, '\n')

            try:
                cur.execute(sql.format(isbn13.lower(), isbn10.lower()))
                cnt += 1
            except Exception as e:
                with open(r'E:\lqx\cover\error.txt', mode='a', encoding='utf8') as f:
                    print(e)
                    f.write(isb + '\n')
                    continue

        if cnt % 1000 == 1:
            time_end = time.time()
            print('time cost:', time_end - time_start)
            print('cnt: %d' % cnt)
            conn.commit()
    time_end = time.time()
    print('total: %d' % cnt)
    print('total time cost:', time_end - time_start)
    conn.commit()


def coverlistcheck():
    file = open(r'C:\Users\xuzhu\Desktop\isbn.txt', 'r')
    time_start = time.time()
    cnt = 0
    for line in file:
        line = line.strip()
        # if isbnlib.is_isbn10(line):
        #     sql2 = "update cover_nopic set stat=1 where isbn10='{}' or isbn13='{}'"
        #     cur.execute(sql2.format(line,line))
        # elif isbnlib.is_isbn13(line):
        #     sql2 = "update cover_nopic set stat=1 where isbn13='{}'"
        #     cur.execute(sql2.format(line))
        sql2 = "update cover_nopic set stat=1 where isbn10='{}' or isbn13='{}'"
        cur.execute(sql2.format(line, line))
        cnt += 1
        if cnt % 1000 == 1:
            conn.commit()
            print('cnt: %d' % cnt)
            time_end = time.time()
            print('time cost:', time_end - time_start)
    print('total: %d' % cnt)
    conn.commit()
    time_end = time.time()
    print('total time cost:', time_end - time_start)


coverlistinsert()
# coverlistcheck()
